﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Classes.Banco
{
    public class clsBanco
    {
        public string Strconexao;
        public MySqlCommand ComandoSql;
        public MySqlConnection conexao;
        DataTable dtBanco = new DataTable();
        MySqlConnection cn = new MySqlConnection();
        MySqlCommand cmd = new MySqlCommand();

        //String conexao
        public clsBanco()
        {
            Strconexao = @"Server=localhost;user id=root;password=cs102010;database=brasilsolutions";
            //Strconexao = stringConexao; //ConfigurationManager.ConnectionStrings["NomeDoAcesso"].ConnectionString;
            conexao = new MySqlConnection(Strconexao);
        }

        //Abrir Banco
        private MySqlConnection AbrirBanco()
        {
            conexao.Open();
            return conexao;
        }

        //Fecahr Banco
        public void FecharBanco(MySqlConnection conexao)
        {
            if (conexao.State == ConnectionState.Open)
                conexao.Close();
        }


        //Método para execução de comando
        public void ExecultarComando(string strQuery)
        {
            try
            {
                cn = AbrirBanco();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conexao;
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                FecharBanco(conexao);
            }
        }

        //Método para execução de comando
        public void ExecultarComando(MySqlCommand sql)
        {
            try
            {
                cn = AbrirBanco();
                sql.Connection = cn;
                sql.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                FecharBanco(conexao);
            }
        }

        //Método que retorna um objeto DataSet
        public DataSet RetonarDataSet(string strQuery)
        {
            try
            {
                cn = AbrirBanco();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conexao;
                cmd.ExecuteNonQuery();

                /*  Declarado um dataadapter e um dataset
               passar o comando para o da (SqlDataAdapter) e
               carregar o dataset com resultado da busca */
                MySqlDataAdapter da = new MySqlDataAdapter();
                DataSet ds = new DataSet();
                da.SelectCommand = cmd;
                da.Fill(ds);
                return ds;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                FecharBanco(conexao);
            }
        }

        //Método para retornar um DataReader()
        public MySqlDataReader RetornarDataReader(string strQuery)
        {
            try
            {
                cn = AbrirBanco();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conexao;
                return cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                FecharBanco(conexao);
            }
        }

        //Método para retornar um Id Numérico
        public int RetornarIdNumerico(string strQuery)
        {
            try
            {
                cn = AbrirBanco();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cn;
                MySqlDataReader dr = cmd.ExecuteReader();
                int id;
                if (dr.Read())
                    id = Convert.ToInt16(dr[0]) + 1;
                else
                    id = 1;
                return id;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                FecharBanco(cn);
            }
        }

        //Método para retornar um DataTable
        public DataTable RetornarDataTable(string strQuery)
        {
            try
            {
                cn = AbrirBanco();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cn;
                MySqlDataAdapter da = new MySqlDataAdapter();
                DataTable ds = new DataTable();
                da.SelectCommand = cmd;
                da.SelectCommand.CommandTimeout = TimeSpan.FromMinutes(3600).Seconds;
                da.Fill(ds);
                return ds;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                FecharBanco(cn);
            }
        }

        //Método para retornar um DataTable
        public DataTable RetornarDataTable(MySqlCommand sql)
        {
            try
            {
                cn = AbrirBanco();

                sql.Connection = cn;
                MySqlDataAdapter da = new MySqlDataAdapter();
                DataTable ds = new DataTable();
                da.SelectCommand = sql;
                da.SelectCommand.CommandTimeout = TimeSpan.FromMinutes(3600).Seconds;
                da.Fill(ds);
                return ds;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                FecharBanco(cn);
            }
        }
    }
}
